﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Globalization;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using PoliceHealthClass;
using PoliceHealthImportSystem.Class;
using Excel = Microsoft.Office.Interop.Excel;
// included for developing

namespace PoliceHealthImportSystem
{
    public partial class frmImportPoliceHealthData : Form
    {
        ~frmImportPoliceHealthData()
        {
            GC.Collect();
        }

        private ManageImportPoliceHealthData manageImportPoliceHelathData = new ManageImportPoliceHealthData();

        delegate void UpdateDelegate(int Progress);

        // Datatable for success and fail import data base
        private DataTable successImportPolicesDt = new DataTable();
        private DataTable failImportPolicesDt = new DataTable();

        //Thai mount name
        private Dictionary<string, int> thaiMonths = new Dictionary<string, int>();
     

        #region Method

        private void UpdateProgress(int Progress)
        {
            if (this.InvokeRequired)
                this.Invoke((UpdateDelegate)UpdateProgress, Progress);
            else
                this.progressBar1.Value = Progress;
        }

        private void GenarateThaimounths()
        {
            thaiMonths.Clear();
            thaiMonths.Add("ม.ค.,มกราคม", 1);
            thaiMonths.Add("ก.พ.,กุมภาพันธ์", 2);
            thaiMonths.Add("มี.ค.,มีนาคม", 3);
            thaiMonths.Add("เม.ย.,เมษายน", 4);
            thaiMonths.Add("พ.ค.,พฤษภาคม", 5);
            thaiMonths.Add("มิ.ย.,มิถุนายน", 6);
            thaiMonths.Add("ก.ค.,กรกฎาคม", 7);
            thaiMonths.Add("ส.ค.,สิงหาคม", 8);
            thaiMonths.Add("ก.ย.,กันยายน", 9);
            thaiMonths.Add("ต.ค.,ตุลาคม", 10);
            thaiMonths.Add("พ.ย.,พฤศจิกายน", 11);
            thaiMonths.Add("ธ.ค.,ธันวาคม", 12);          
          
        }

        private int GetThaiMonthByThaiShortName(string shortName)
        {
            int month = 0;

            foreach (KeyValuePair<string, int> pair in thaiMonths)
            {
                if (pair.Key.Contains(shortName.Trim())) return pair.Value;
            }

            return month;
        }

        private void GenerateOrganization(TreeNode trvInput, string ParentOrganizationID)
        {
            PoliceHealthClass.Organization OrganizationBuffer = new PoliceHealthClass.Organization();
            ArrayList OrganizationArr = OrganizationBuffer.getOrganizationChilds(ParentOrganizationID);

            if (OrganizationArr.Count > 0)
            {
                foreach (Organization org in OrganizationArr)
                {
                    TreeNode trvBuffer = new TreeNode(org.organization_name_tha);
                    trvBuffer.Tag = org.organization_id;
                    trvInput.Nodes.Add(trvBuffer);
                    GenerateOrganization(trvBuffer, org.organization_id);
                }
            }
        }

        public int GetPreNameIdByThaiShortName(string shortThaiPreName, DataTable prenameDataTable)
        {
            int prenameId = 0;
            Prename objPrename = new Prename();
            prenameId = objPrename.GetPrenameByShortNameThai(shortThaiPreName, prenameDataTable);
            return prenameId;
        }

        private bool IsSelectValidOrganizatonTreeNode()
        {
            return (trvImportOrganization.SelectedNode != null && trvImportOrganization.SelectedNode.LastNode == null);
        }

        private string GetSelectedOrganizationID()
        {
            return trvImportOrganization.SelectedNode.Tag.ToString();
        }

        private string GetGenderByShortPrename(string shortPrename)
        {
            string gender = string.Empty;

            if (shortPrename.Contains("ญ") || shortPrename.Contains("นาง") || shortPrename.Contains("หญิง"))
            {
                gender = "F";
            }
            else if (shortPrename == "" || shortPrename == "-")
            {
                gender = "U";
            }
            else
            {
                gender = "M";
            }

            return gender;
        }

        private void PrepareSuccessAndFailDataTableColumn()
        {
            successImportPolicesDt.Columns.Add("Order", typeof(string));
            successImportPolicesDt.Columns.Add("CitizenId", typeof(string));
            successImportPolicesDt.Columns.Add("NameSurname", typeof(string));

            failImportPolicesDt.Columns.Add("Order", typeof(string));
            failImportPolicesDt.Columns.Add("CitizenId", typeof(string));
            failImportPolicesDt.Columns.Add("NameSurname", typeof(string));
            failImportPolicesDt.Columns.Add("Reason", typeof(string));
        }

        private void BindSuccessAndFailGrid()
        {
            // Bind to success grid
            dgvImportSuccess.AutoGenerateColumns = false;
            dgvImportSuccess.Columns["successNumber"].DataPropertyName = "Order";
            dgvImportSuccess.Columns["successCitizenId"].DataPropertyName = "CitizenId";
            dgvImportSuccess.Columns["successFullName"].DataPropertyName = "NameSurname";
            dgvImportSuccess.DataSource = successImportPolicesDt;

            // Bind to fail grid
            dgvImportFail.AutoGenerateColumns = false;
            dgvImportFail.Columns["failNumber"].DataPropertyName = "Order";
            dgvImportFail.Columns["failCitizenId"].DataPropertyName = "CitizenId";
            dgvImportFail.Columns["failFullName"].DataPropertyName = "NameSurname";
            dgvImportFail.Columns["failCause"].DataPropertyName = "Reason";
            dgvImportFail.DataSource = failImportPolicesDt;
        }

        private void RemoveDataRowFromDataSuccessTable(string citizenId)
        {
            if (!string.IsNullOrEmpty(citizenId))
            {
                DataRow[] policeRows = successImportPolicesDt.Select("CitizenId = '" + citizenId  +"'");
                for (int i = policeRows.Length - 1; i >= 0; i--)
                {
                    successImportPolicesDt.Rows.Remove(policeRows[i]);
                }
                successImportPolicesDt.AcceptChanges();
            }
        }

        private bool IsNeedToSetMinValue(string data)
        {
            bool isNeedToSetMinValue = false;
            if (string.IsNullOrEmpty(data) || data.Trim() == "-")
            {
                isNeedToSetMinValue = true;
            }
            return isNeedToSetMinValue;
        }

        private void ImportDataFromExcelToTable()
        {
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

            // ExcelReader spreadSheet = new ExcelReader();
            SetStatus("กำลังอ่านข้อมูลจากไฟล์");

            DataTable policeDataFromExcel = GetDataTableFromExcel(this.openFileDialog.FileName, 5);

            int toTalRecord = policeDataFromExcel.Rows.Count;

            if (IsCorrectTemplate(policeDataFromExcel))
            {
                ClearDataGridAndDataTable();

                int rowCounter = 1;
                int currentPercent = 0;

                #region foreach loop

                SetStatus("กำลังนำเข้าข้อมูล");

                Police objPolice = new Police();
                string selectedYear = cbbImportYear.SelectedValue.ToString();
                string selectedOrganizationId = GetSelectedOrganizationID();
                string selectedOrganzationName = trvImportOrganization.SelectedNode.Text;

                //if (selectedYear != DateTime.Now.Year.ToString())
                //{
                //    if (MessageBox.Show("เนื่องจากนำข้อมูลเข้าปีย้อนหลัง โปรแกรมจำทำการลบข้อมูลปีปัจจุบัน และปีที่นำเข้า ต้องการดำเนินงานต่อหรือไม่?", "คำถาม", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
                //    {
                //        for (int i = 0; i <= 1; i++)
                //        {
                //            Police.DeleteAllPoliceByOrganization((DateTime.Now.Year - i).ToString(), selectedOrganizationId);
                //        }
                //    }
                //}

                DataTable allPolicesDt = new DataTable();
                allPolicesDt = objPolice.GetAllPoliceByYearAndOrganization(selectedYear, selectedOrganizationId).Tables[0];

                Prename tmpPrename = new Prename();
                DataTable allPrename = new DataTable();
                allPrename = tmpPrename.GetAllPrename();

                foreach (DataRow row in policeDataFromExcel.Rows)
                {
                    bool isValidDataAndReadyToImportPoliceTable = true;
                    bool duplicatePoliceDataStatus = true;
                    bool isNeedtoUpdatePoliceData = false;

                    currentPercent = (rowCounter * 100) / toTalRecord;

                    UpdateProgress(currentPercent);

                    SetPageProgressText(rowCounter, toTalRecord);

                    Police police = new Police();
                    string citizenId = row[5].ToString();
                    string fullPoliceName = row[1].ToString() + " " + row[2].ToString() + " - " + row[3].ToString();
                    string orderNumber = row[0].ToString();
                    string shortPrename = row[1].ToString();

                    // Police Age
                    string strPoliceAge = row[4].ToString();
                    int intPoliceAge;
                    bool isPoliceAgeConvertsionSuccess = int.TryParse(strPoliceAge, out intPoliceAge);

                    isValidDataAndReadyToImportPoliceTable = true;                                  

                    duplicatePoliceDataStatus = police.CheckImportPoliceData(selectedYear, citizenId, allPolicesDt);

                    // If duplicate police data add to fail table
                    if (duplicatePoliceDataStatus)
                    {
                        Police tempPolice = police.getPoliceByCitizenId(citizenId);

                        if (tempPolice != null && tempPolice.organization != null && selectedOrganizationId == tempPolice.organization.organization_id)
                        {
                            // same organization sholud update
                            isValidDataAndReadyToImportPoliceTable = true;
                            isNeedtoUpdatePoliceData = true;
                        }
                        else
                        {
                            isValidDataAndReadyToImportPoliceTable = false;
                            isNeedtoUpdatePoliceData = false;
                            // Add to fail import list
                            failImportPolicesDt.Rows.Add(orderNumber, citizenId, fullPoliceName, "ไม่สามารถนำเข้า tbpolice ได้เนื่องจาก สังกัดไม่ตรงกับปีที่แล้ว กรุณาเปลี่ยนสังกัด ในโปรแกรมก่อน เพื่อเป็นการยืนยันว่าเจ้าพนักงานได้ย้ายสังกัดในปีนี้");
                        }
                    }                 
                
                    if (isValidDataAndReadyToImportPoliceTable)
                    {
                        // Date of birth month
                        string dobErrorMessage = string.Empty;
                        bool isValidDOB = true;                 
                                                   
                        // Check date of birth is correct
                        int dobDay = 0;
                        if (!string.IsNullOrEmpty(row[6].ToString()))
                        {
                            dobDay = Int32.Parse(row[6].ToString());
                        }
                        else 
                        {
                            dobErrorMessage += " วัน ";
                            isValidDOB = false;
                        }

                        int dobMonth = 0;
                        string strDobMonth = row[7].ToString();

                        if (!string.IsNullOrEmpty(strDobMonth))
                        {
                            int intDobMonth;
                            bool isDobMonthConvertsionSuccess = int.TryParse(strDobMonth, out intDobMonth);

                            if (isDobMonthConvertsionSuccess)
                            {
                                dobMonth = intDobMonth;
                            }
                            else
                            {
                                dobMonth = GetThaiMonthByThaiShortName(strDobMonth);
                            }
                        }
                        else
                        {
                            dobErrorMessage += "เดือน ";
                            isValidDOB = false;
                        }   

                        int dobYear = 0;
                        if (!string.IsNullOrEmpty(row[8].ToString()))
                        {
                             dobYear = Int32.Parse(row[8].ToString()) - 543;
                        }
                        else
                        {
                            dobErrorMessage += "ปี ";
                            isValidDOB = false;
                        }

                        if (isValidDOB)
                        {
                            // month / day /year
                            string strDOBDate = dobMonth.ToString() + "/" + row[6].ToString() + "/" + dobYear.ToString();

                            DateTime dob;

                            if (DateTime.TryParse(strDOBDate, out dob))
                            {
                                //check is  date of birth not over 100
                                int yearDateDiff = DateTime.Now.Year - dob.Year;

                                if (yearDateDiff > 100)
                                {
                                    failImportPolicesDt.Rows.Add(orderNumber, citizenId, fullPoliceName, "ข้อมูลวันเดือนปีเกิดผิด อายุคำนวณได้มากกว่า 100 ปี");
                                    isValidDataAndReadyToImportPoliceTable = false;
                                }
                                else
                                {
                                    police.birth_date = dob;
                                }
                            }
                            else
                            {
                                failImportPolicesDt.Rows.Add(orderNumber, citizenId, fullPoliceName, "ข้อมูลวันเดือนปีเกิดผิด");
                                isValidDataAndReadyToImportPoliceTable = false;
                            }
                        }
                        else {
                            failImportPolicesDt.Rows.Add(orderNumber, citizenId, fullPoliceName, "ไม่มีข้อมูล " +dobErrorMessage +"เกิด");
                            isValidDataAndReadyToImportPoliceTable = false;
                        }


                        if (isValidDataAndReadyToImportPoliceTable)
                        {
                            #region Import Police Table

                            police.citizen_id = citizenId;
                            //Prename
                            Prename objPrename = new Prename();
                            objPrename.prename_id = GetPreNameIdByThaiShortName(shortPrename, allPrename);
                            police.prename = objPrename;

                            //other information
                            police.create_date = DateTime.Now;
                            police.gender = GetGenderByShortPrename(shortPrename);
                            police.folder_path = null;
                            police.commentary = null;
                            police.picture_name = null;
                            police.create_user = "Import Program";
                            police.update_user = "Import Program";
                            police.update_date = DateTime.Now;

                            //Name and surname
                            police.name = row[2].ToString();
                            police.surname = row[3].ToString();

                            //Organization
                            Organization objOrganization = new Organization();

                            objOrganization.organization_id = selectedOrganizationId;
                            objOrganization.organization_name_tha = selectedOrganzationName;

                            police.organization = objOrganization;

                            //WorkStatus
                            WorkStatus objWorkStatus = new WorkStatus();
                            objWorkStatus.work_status_id = 1;
                            police.work_status = objWorkStatus;

                            // Check can insert to database or not
                            bool isInsertOrUpdatePoliceTableSuccess;

                            if (isNeedtoUpdatePoliceData)
                            {
                                isInsertOrUpdatePoliceTableSuccess = police.updatePolice(police, Convert.ToInt32(selectedYear) - 543);
                            }
                            else
                            {
                                isInsertOrUpdatePoliceTableSuccess = police.insertPolice(police, Convert.ToInt32(selectedYear) - 543);
                            }

                            if (isInsertOrUpdatePoliceTableSuccess)
                            {

                                bool isCanContinueAfterImpportPolicesuccess = true;
                                // Add to success list
                                successImportPolicesDt.Rows.Add(orderNumber, citizenId, fullPoliceName);

                                #region Add to BiochemistryResultHistory

                                if (isCanContinueAfterImpportPolicesuccess)
                                {
                                    BiochemistryResultHistory biochemistryResultHistory = new BiochemistryResultHistory();
                                    //Year
                                    biochemistryResultHistory.year = selectedYear;

                                    // Full name and age
                                    biochemistryResultHistory.police_fullname = fullPoliceName;
                                    biochemistryResultHistory.police = police;

                                    // Police Age
                                    if (isPoliceAgeConvertsionSuccess)
                                    {
                                        biochemistryResultHistory.police_age = intPoliceAge;
                                    }
                                    else if (IsNeedToSetMinValue(strPoliceAge))
                                    {
                                        biochemistryResultHistory.police_age = int.MinValue;
                                    }


                                    // glucose
                                    string strGlucose = row[64].ToString();
                                    int intGlucose;
                                    bool isGlucoseConvertsionSuccess = int.TryParse(strGlucose, out intGlucose);
                                    if (isGlucoseConvertsionSuccess)
                                    {
                                        biochemistryResultHistory.glucose = intGlucose;
                                    }
                                    else if (IsNeedToSetMinValue(strGlucose))
                                    {
                                        biochemistryResultHistory.glucose = int.MinValue;
                                    }

                                    // crea
                                    string strCrea = row[65].ToString();
                                    double dobCrea;
                                    bool isCreaConvertsionSuccess = double.TryParse(strCrea, out dobCrea);
                                    if (isCreaConvertsionSuccess)
                                    {
                                        biochemistryResultHistory.crea = dobCrea;
                                    }
                                    else if (IsNeedToSetMinValue(strCrea))
                                    {
                                        biochemistryResultHistory.crea = double.MinValue;
                                    }

                                    // Urea
                                    string strUrea = row[66].ToString();
                                    int intUrea;
                                    bool isUreaConvertsionSuccess = int.TryParse(strUrea, out intUrea);
                                    if (isUreaConvertsionSuccess)
                                    {
                                        biochemistryResultHistory.urea = intUrea;
                                    }
                                    else if (IsNeedToSetMinValue(strUrea))
                                    {
                                        biochemistryResultHistory.urea = int.MinValue;
                                    }

                                    // Uric Acid
                                    string strUricAcid = row[67].ToString();
                                    double dobUricAcid;
                                    bool isUricAcidConvertsionSuccess = double.TryParse(strUricAcid, out dobUricAcid);
                                    if (isUricAcidConvertsionSuccess)
                                    {
                                        biochemistryResultHistory.uric_acid = dobUricAcid;
                                    }
                                    else if (IsNeedToSetMinValue(strUricAcid))
                                    {
                                        biochemistryResultHistory.uric_acid = double.MinValue;
                                    }
                                    // Chol
                                    string strChol = row[68].ToString();
                                    int intChol;
                                    bool isCholConvertsionSuccess = int.TryParse(strChol, out intChol);
                                    if (isCholConvertsionSuccess)
                                    {
                                        biochemistryResultHistory.chol = intChol;
                                    }
                                    else if (IsNeedToSetMinValue(strChol))
                                    {
                                        biochemistryResultHistory.chol = int.MinValue;
                                    }

                                    // Tg
                                    string strTg = row[69].ToString();
                                    int intTg;
                                    bool isTgConvertsionSuccess = int.TryParse(strTg, out intTg);
                                    if (isTgConvertsionSuccess)
                                    {
                                        biochemistryResultHistory.tg = intTg;
                                    }
                                    else if (IsNeedToSetMinValue(strTg))
                                    {
                                        biochemistryResultHistory.tg = int.MinValue;
                                    }

                                    // HDL
                                    string strHDL = row[70].ToString();
                                    int intHDL;
                                    bool isHDLConvertsionSuccess = int.TryParse(strHDL, out intHDL);
                                    if (isHDLConvertsionSuccess)
                                    {
                                        biochemistryResultHistory.hdl = intHDL;
                                    }
                                    else if (IsNeedToSetMinValue(strHDL))
                                    {
                                        biochemistryResultHistory.hdl = int.MinValue;
                                    }
                                    //LDL
                                    string strLDL = row[71].ToString();
                                    biochemistryResultHistory.ldl = strLDL;

                                    // SGOT
                                    string strSGOT = row[72].ToString();
                                    int intSGOT;
                                    bool isSGOTConvertsionSuccess = int.TryParse(strSGOT, out intSGOT);
                                    if (isSGOTConvertsionSuccess)
                                    {
                                        biochemistryResultHistory.sgol = intSGOT;
                                    }
                                    else if (IsNeedToSetMinValue(strSGOT))
                                    {
                                        biochemistryResultHistory.sgol = int.MinValue;
                                    }

                                    // SGPT
                                    string strSGPT = row[73].ToString();
                                    int intSGPT;
                                    bool isSGPTConvertsionSuccess = int.TryParse(strSGPT, out intSGPT);
                                    if (isSGPTConvertsionSuccess)
                                    {
                                        biochemistryResultHistory.sgpt = intSGPT;
                                    }
                                    else if (IsNeedToSetMinValue(strSGPT))
                                    {
                                        biochemistryResultHistory.sgpt = int.MinValue;
                                    }

                                    // Alkalinephos
                                    string strAlkalinephos = row[74].ToString();
                                    int intAlkalinephos;
                                    bool isAlkalinephosConvertsionSuccess = int.TryParse(strAlkalinephos, out intAlkalinephos);
                                    if (isAlkalinephosConvertsionSuccess)
                                    {
                                        biochemistryResultHistory.alkaline_phos = intAlkalinephos;
                                    }
                                    else if (IsNeedToSetMinValue(strAlkalinephos))
                                    {
                                        biochemistryResultHistory.alkaline_phos = int.MinValue;
                                    }

                                    //Alkaline Phos Liquid
                                    AlkalinePhosLiquid alkalinePhosLiquid = new AlkalinePhosLiquid();
                                    alkalinePhosLiquid.alkaline_phos_liquid_id = 1;
                                    biochemistryResultHistory.alkaline_phos_liquid = alkalinePhosLiquid;

                                    // Lead person
                                    LeadPerson leadPerson = new LeadPerson();
                                    leadPerson.lead_person_id = 0;
                                    biochemistryResultHistory.lead_person = leadPerson;

                                    // Lead
                                    biochemistryResultHistory.lead = 0;

                                    //Insert BiochemistryResultHistory

                                    bool isInsertBiochemistryResultHistorySuccess = biochemistryResultHistory.insertPoliceBiochemistryResultHistory(biochemistryResultHistory);

                                    if (isInsertBiochemistryResultHistorySuccess)
                                    {
                                        biochemistryResultHistory.insertBiochemistryAbnormalSummaryReportValue(selectedYear, selectedOrganizationId);
                                        biochemistryResultHistory.insertBiochemistrySummaryReportValue(selectedYear, selectedOrganizationId);
                                    }
                                    else
                                    {
                                        isCanContinueAfterImpportPolicesuccess = false;

                                        failImportPolicesDt.Rows.Add(orderNumber, citizenId, fullPoliceName, "ไม่สามารถบันทึกข้อมูลผลการตรวจชีวเคมีได้");
                                        // delete police is import year fail
                                        police.DeleteAllPoliceImportDataFail(selectedYear, police);
                                        police.deletePoliceImportDataFail(selectedYear, citizenId);
                                        //also delete from success table
                                        RemoveDataRowFromDataSuccessTable(citizenId);
                                    }
                                }

                                #endregion

                                #region Add thblood_result_history

                                if (isCanContinueAfterImpportPolicesuccess)
                                {
                                    BloodResultHistory bloodResultHistory = new BloodResultHistory();

                                    // Hb
                                    string strHb = row[46].ToString();
                                    double douHb;
                                    bool isHbConvertsionSuccess = double.TryParse(strHb, out douHb);
                                    if (isHbConvertsionSuccess)
                                    {
                                        bloodResultHistory.hb = douHb;
                                    }
                                    else if (IsNeedToSetMinValue(strHb))
                                    {
                                        bloodResultHistory.hb = double.MinValue;
                                    }

                                    // Hct
                                    string strHct = row[47].ToString();
                                    int intHct;
                                    bool isHctConvertsionSuccess = int.TryParse(strHct, out intHct);
                                    if (isHctConvertsionSuccess)
                                    {
                                        bloodResultHistory.hct = intHct;
                                    }
                                    else if (IsNeedToSetMinValue(strHct))
                                    {
                                        bloodResultHistory.hct = int.MinValue;
                                    }

                                    // Wbc
                                    string strWbc = row[48].ToString();
                                    strWbc = String.IsNullOrEmpty(strWbc) ? String.Empty : strWbc.Replace(",", "");
                                    int intWbc;
                                    bool isWbcConvertsionSuccess = int.TryParse(strWbc, out intWbc);
                                    if (isWbcConvertsionSuccess)
                                    {
                                        bloodResultHistory.wbc = intWbc;
                                    }
                                    else if (IsNeedToSetMinValue(strWbc))
                                    {
                                        bloodResultHistory.wbc = int.MinValue;
                                    }

                                    // Neu
                                    string strNeu = row[49].ToString();
                                    int intNeu;
                                    bool isNeuConvertsionSuccess = int.TryParse(strNeu, out intNeu);
                                    if (isNeuConvertsionSuccess)
                                    {
                                        bloodResultHistory.neu = intNeu;
                                    }
                                    else if (IsNeedToSetMinValue(strNeu))
                                    {
                                        bloodResultHistory.neu = int.MinValue;
                                    }

                                    // Eosin
                                    string strEosin = row[50].ToString();
                                    int intEosin;
                                    bool isEosinConvertsionSuccess = int.TryParse(strEosin, out intEosin);
                                    if (isEosinConvertsionSuccess)
                                    {
                                        bloodResultHistory.eosin = intEosin;
                                    }
                                    else if (IsNeedToSetMinValue(strEosin))
                                    {
                                        bloodResultHistory.eosin = int.MinValue;
                                    }

                                    // Baso
                                    string strBaso = row[51].ToString();
                                    int intBaso;
                                    bool isBasoConvertsionSuccess = int.TryParse(strBaso, out intBaso);
                                    if (isBasoConvertsionSuccess)
                                    {
                                        bloodResultHistory.baso = intBaso;
                                    }
                                    else if (IsNeedToSetMinValue(strBaso))
                                    {
                                        bloodResultHistory.baso = int.MinValue;
                                    }

                                    // Lymph
                                    string strLymph = row[52].ToString();
                                    int intLymph;
                                    bool isLymphConvertsionSuccess = int.TryParse(strLymph, out intLymph);
                                    if (isLymphConvertsionSuccess)
                                    {
                                        bloodResultHistory.lymph = intLymph;
                                    }
                                    else if (IsNeedToSetMinValue(strLymph))
                                    {
                                        bloodResultHistory.lymph = int.MinValue;
                                    }

                                    // Mono
                                    string strMono = row[53].ToString();
                                    int intMono;
                                    bool isMonoConvertsionSuccess = int.TryParse(strMono, out intMono);
                                    if (isMonoConvertsionSuccess)
                                    {
                                        bloodResultHistory.mono = intMono;
                                    }
                                    else if (IsNeedToSetMinValue(strMono))
                                    {
                                        bloodResultHistory.mono = int.MinValue;
                                    }

                                    // Plt
                                    string strPlt = row[54].ToString();
                                    strPlt = String.IsNullOrEmpty(strPlt) ? String.Empty : strPlt.Replace(",", "");
                                    int intPlt;
                                    bool isPltConvertsionSuccess = int.TryParse(strPlt, out intPlt);
                                    if (isPltConvertsionSuccess)
                                    {
                                        bloodResultHistory.plt = intPlt;
                                    }
                                    else if (IsNeedToSetMinValue(strPlt))
                                    {
                                        bloodResultHistory.plt = int.MinValue;
                                    }

                                    // General Info
                                    if (isPoliceAgeConvertsionSuccess)
                                    {
                                        bloodResultHistory.police_age = short.Parse(intPoliceAge.ToString());
                                    }
                                    bloodResultHistory.police = police;
                                    bloodResultHistory.police_fullname = fullPoliceName;
                                    bloodResultHistory.year = selectedYear;

                                    //Insert Blood Result History
                                    bool isInsertBloodResultHistorySuccess = bloodResultHistory.insertPoliceBloodResultHistory(bloodResultHistory);

                                    if (isInsertBloodResultHistorySuccess)
                                    {
                                        bloodResultHistory.insertCBCAbnormalSummaryReportValue(selectedYear, selectedOrganizationId);
                                        bloodResultHistory.insertCBCSummaryReportValue(selectedYear, selectedOrganizationId);
                                    }
                                    else
                                    {
                                        isCanContinueAfterImpportPolicesuccess = false;

                                        failImportPolicesDt.Rows.Add(orderNumber, citizenId, fullPoliceName, "ไม่สามารถบันทึกข้อมูลผลการตรวจเลือดได้");
                                        // delete police is import year fail
                                        police.DeleteAllPoliceImportDataFail(selectedYear, police);
                                        police.deletePoliceImportDataFail(selectedYear, citizenId);
                                        //also delete from success table
                                        RemoveDataRowFromDataSuccessTable(citizenId);
                                    }
                                }

                                #endregion

                                #region Add thbmi_result_history
                                ////check
                                if (isCanContinueAfterImpportPolicesuccess)
                                {

                                    BMIResultHistory bmiResultHistory = new BMIResultHistory();
                                    //Year 
                                    bmiResultHistory.year = selectedYear;

                                    //BP
                                    string strBP = row[31].ToString();
                                    strBP = String.IsNullOrEmpty(strBP) ? String.Empty : strBP.Replace(" ", "");
                                    bmiResultHistory.blood_pressure = String.IsNullOrEmpty(strBP) ? null : strBP;

                                    // Pulse
                                    string strPulse = row[32].ToString();
                                    int intPulse;
                                    bool isPulseConvertsionSuccess = int.TryParse(strPulse, out intPulse);
                                    if (isPulseConvertsionSuccess)
                                    {
                                        bmiResultHistory.pluse = intPulse;
                                    }
                                    else if (IsNeedToSetMinValue(strPulse))
                                    {
                                        bmiResultHistory.pluse = int.MinValue;
                                    }

                                    // Weight
                                    string strWeight = row[33].ToString();
                                    double dobWeight;
                                    bool isWeightConvertsionSuccess = double.TryParse(strWeight, out dobWeight);
                                    if (isWeightConvertsionSuccess)
                                    {
                                        bmiResultHistory.weight = dobWeight;
                                    }
                                    else if (IsNeedToSetMinValue(strWeight))
                                    {
                                        bmiResultHistory.weight = double.MinValue;
                                    }

                                    // Height
                                    string strHeight = row[34].ToString();
                                    double dobHeight;
                                    bool isHeightConvertsionSuccess = double.TryParse(strHeight, out dobHeight);
                                    if (isHeightConvertsionSuccess)
                                    {
                                        bmiResultHistory.hight = dobHeight;
                                    }
                                    else if (IsNeedToSetMinValue(strHeight))
                                    {
                                        bmiResultHistory.hight = double.MinValue;
                                    }

                                    // BMI
                                    string strBMI = row[36].ToString();
                                    double dobBMI;
                                    bool isBMIConvertsionSuccess = double.TryParse(strBMI, out dobBMI);
                                    if (isBMIConvertsionSuccess)
                                    {
                                        bmiResultHistory.bmi = dobBMI;
                                    }
                                    else if (IsNeedToSetMinValue(strBMI))
                                    {
                                        bmiResultHistory.bmi = double.MinValue;
                                    }

                                    //Police
                                    bmiResultHistory.Police = police;
                                    bmiResultHistory.police_full_name = fullPoliceName;
                                    bmiResultHistory.year = selectedYear;
                                    if (isPoliceAgeConvertsionSuccess)
                                    {
                                        bmiResultHistory.police_age = intPoliceAge;
                                    }
                                    else if (IsNeedToSetMinValue(strPoliceAge))
                                    {
                                        bmiResultHistory.police_age = int.MinValue;
                                    }

                                    //Insert BMIResultHistory

                                    bool isInsertBMIResultHistorySuccess = bmiResultHistory.insertPoliceBMIResultHistory(bmiResultHistory);

                                    if (isInsertBMIResultHistorySuccess)
                                    {
                                        bmiResultHistory.insertBMIAbnormalSummaryReportValue(selectedYear, selectedOrganizationId);
                                        bmiResultHistory.insertBMISummaryReportValue(selectedYear, selectedOrganizationId);
                                    }
                                    else
                                    {
                                        isCanContinueAfterImpportPolicesuccess = false;

                                        failImportPolicesDt.Rows.Add(orderNumber, citizenId, fullPoliceName, "ไม่สามารถบันทึกข้อมูลผลการ BMI ได้");
                                        // delete police is import year fail
                                        police.DeleteAllPoliceImportDataFail(selectedYear, police);
                                        police.deletePoliceImportDataFail(selectedYear, citizenId);
                                        //also delete from success table
                                        RemoveDataRowFromDataSuccessTable(citizenId);
                                    }
                                }

                                #endregion

                                #region EKGResultHistory
                                ////check
                                if (isCanContinueAfterImpportPolicesuccess)
                                {
                                    EKGResultHistory eKGResultHistory = new EKGResultHistory();

                                    bool isInsertEKGResultHistorySuccess = false;

                                    string strEKG = row[87].ToString();

                                    //1. if excel is string empty or null create EKGResultHistory instance (set properties by person value but set ekg_result_status = int.minvalue)
                                    if (string.IsNullOrEmpty(strEKG))
                                    {
                                        eKGResultHistory.ekg_result_status = short.MinValue;
                                    }
                                    else if (strEKG == "ปกติ")
                                    {
                                        //if excel is "ปกติ" create EKGResultHistory instance (set properties by person value but set ekg_result_status = 1)
                                        eKGResultHistory.ekg_result_status = 1;
                                    }
                                    else
                                    {
                                        // if excel is not "ปกติ" create EKGResultHistory instance (set properties by person value but set ekg_result_status = 0)
                                        eKGResultHistory.ekg_result_status = 0;
                                    }

                                    // General info
                                    eKGResultHistory.police = police;
                                    eKGResultHistory.police_fullname = fullPoliceName;
                                    eKGResultHistory.year = selectedYear;

                                    if (isPoliceAgeConvertsionSuccess)
                                    {
                                        eKGResultHistory.police_age = intPoliceAge;
                                    }
                                    else if (IsNeedToSetMinValue(strPoliceAge))
                                    {
                                        eKGResultHistory.police_age = int.MinValue;
                                    }

                                    if (eKGResultHistory.ekg_result_status == 0)
                                    {
                                        // if excel is not "ปกติ" create EKGAbnormal instance and use EKGAbnormal.getsEKGAbnormalByNameEng() to find ekg abnormal 

                                        EKGAbnormal objEKGAbnormal = new EKGAbnormal();

                                        EKGAbnormal eKGAbnormal = objEKGAbnormal.getsEKGAbnormalByNameEng(strEKG);

                                        //3.1.1 if found create EKGAbnormalResultHistory instance (set all properties) and send it to EKGAbnormalResultHistory.insertEKGAbnormalResultHistory()
                                        if (eKGAbnormal.ekg_abnormal_name_eng != null)
                                        {
                                            EKGAbnormalResultHistory eKGAbnormalResultHistory = new EKGAbnormalResultHistory();
                                            eKGAbnormalResultHistory.ekg_abnormal = eKGAbnormal;
                                            eKGAbnormalResultHistory.police = police;
                                            eKGAbnormalResultHistory.year = selectedYear;
                                            eKGAbnormalResultHistory.insertEKGAbnormalResultHistory(eKGAbnormalResultHistory);
                                        }
                                        else
                                        {
                                            // 3.1.2 if not found create EKGAbnormal instance (set all properties) and send it to EKGAbnormal.insertEKGAbnormal() after that do 3.1.1
                                            EKGAbnormal newEKGAbnormal = new EKGAbnormal();
                                            newEKGAbnormal.ekg_abnormal_id = objEKGAbnormal.getMaxEKGAbnormalID();
                                            newEKGAbnormal.ekg_abnormal_name_eng = strEKG;
                                            newEKGAbnormal.ekg_abnormal_name_tha = null;

                                            bool isInsertNewEKGAbnormalSuccess = eKGAbnormal.insertEKGAbnormal(newEKGAbnormal);

                                            //after that do 3.1.1
                                            if (isInsertNewEKGAbnormalSuccess)
                                            {
                                                EKGAbnormal tempEKGAbnormal = objEKGAbnormal.getsEKGAbnormalByNameEng(strEKG);

                                                EKGAbnormalResultHistory eKGAbnormalResultHistory = new EKGAbnormalResultHistory();
                                                eKGAbnormalResultHistory.ekg_abnormal = tempEKGAbnormal;
                                                eKGAbnormalResultHistory.police = police;
                                                eKGAbnormalResultHistory.year = selectedYear;
                                                isInsertEKGResultHistorySuccess = eKGAbnormalResultHistory.insertEKGAbnormalResultHistory(eKGAbnormalResultHistory);
                                            }
                                        }
                                    }

                                    isInsertEKGResultHistorySuccess = eKGResultHistory.insertPoliceEKGResultHistory(eKGResultHistory);

                                    if (isInsertEKGResultHistorySuccess)
                                    {
                                        //4. after all above process call method EKGResultHistory.insertEKGAbnormalSummaryReportValue() and EKGResultHistory.insertEKGSummaryReportValue()

                                        eKGResultHistory.insertEKGAbnormalSummaryReportValue(selectedYear, selectedOrganizationId);
                                        eKGResultHistory.insertEKGSummaryReportValue(selectedYear, selectedOrganizationId);

                                        isCanContinueAfterImpportPolicesuccess = true;
                                    }
                                    else
                                    {
                                        isCanContinueAfterImpportPolicesuccess = false;

                                        failImportPolicesDt.Rows.Add(orderNumber, citizenId, fullPoliceName, "ไม่สามารถบันทึกข้อมูลผลการคลื่นไฟฟ้าหัวใจได้");
                                        // delete police is import year fail
                                        police.DeleteAllPoliceImportDataFail(selectedYear, police);
                                        police.deletePoliceImportDataFail(selectedYear, citizenId);
                                        //also delete from success table
                                        RemoveDataRowFromDataSuccessTable(citizenId);
                                    }
                                }

                                #endregion

                                #region Import to thlung_result_history
                                ////check
                                if (isCanContinueAfterImpportPolicesuccess)
                                {
                                    LungResult lungResult = new LungResult();

                                    string strLungResult = row[30].ToString();

                                    //If spiro datas == null or white space convert to N/A
                                    if (String.IsNullOrEmpty(strLungResult))
                                    {
                                        lungResult = lungResult.getLungResultByEngName("N/A");
                                    }
                                    else
                                    {
                                        lungResult = lungResult.getLungResultByEngName(strLungResult);
                                    }

                                    LungResultHistory lungResultHistory = new LungResultHistory();
                                    lungResultHistory.LungResult = lungResult;
                                    lungResultHistory.Police = police;
                                    lungResultHistory.police_full_name = fullPoliceName;
                                    if (isPoliceAgeConvertsionSuccess)
                                    {
                                        lungResultHistory.police_age = intPoliceAge;
                                    }
                                    lungResultHistory.year = selectedYear;

                                    bool isInsertlungResultHistorySuccess = lungResultHistory.insertPoliceLungResultHistory(lungResultHistory);

                                    if (!isInsertlungResultHistorySuccess)
                                    {
                                        isCanContinueAfterImpportPolicesuccess = false;

                                        failImportPolicesDt.Rows.Add(orderNumber, citizenId, fullPoliceName, "ไม่สามารถบันทึกข้อมูล Spiro ได้");
                                        // delete police is import year fail
                                        police.DeleteAllPoliceImportDataFail(selectedYear, police);
                                        police.deletePoliceImportDataFail(selectedYear, citizenId);
                                        //also delete from success table
                                        RemoveDataRowFromDataSuccessTable(citizenId);
                                    }


                                }

                                #endregion

                                #region Import to XRayResult
                                ////check
                                if (isCanContinueAfterImpportPolicesuccess)
                                {
                                    XRayResult xRayResult = new XRayResult();

                                    string strXRayResultData = row[88].ToString();

                                    if (string.IsNullOrEmpty(strXRayResultData) || strXRayResultData == "ไม่ได้ตรวจ")
                                    {
                                        xRayResult = xRayResult.getXRayResultByThaiName("ไม่ได้ตรวจ");
                                    }
                                    else
                                    {
                                        xRayResult = xRayResult.getXRayResultByThaiName(strXRayResultData);
                                    }

                                    // XRayResultHistory
                                    XRayResultHistory xRayResultHistory = new XRayResultHistory();

                                    xRayResultHistory.x_ray_result = xRayResult;
                                    xRayResultHistory.police = police;
                                    xRayResultHistory.police_full_name = fullPoliceName;
                                    if (isPoliceAgeConvertsionSuccess)
                                    {
                                        xRayResultHistory.police_age = intPoliceAge;
                                    }
                                    xRayResultHistory.year = selectedYear;

                                    //Insert Blood Result History
                                    bool isInsertBloodResultHistorySuccess = xRayResultHistory.insertPoliceXRayResultHistory(xRayResultHistory);

                                    if (isInsertBloodResultHistorySuccess)
                                    {
                                        isCanContinueAfterImpportPolicesuccess = true;

                                        xRayResultHistory.insertXRayAbnormalSummaryReportValue(selectedYear, selectedOrganizationId);
                                        xRayResultHistory.insertXRaySummaryReportValue(selectedYear, selectedOrganizationId);
                                    }
                                    else
                                    {
                                        isCanContinueAfterImpportPolicesuccess = false;

                                        failImportPolicesDt.Rows.Add(orderNumber, citizenId, fullPoliceName, "ไม่สามารถบันทึกข้อมูลผลเอ็กซเรย์ได้");
                                        // delete police is import year fail
                                        police.DeleteAllPoliceImportDataFail(selectedYear, police);
                                        police.deletePoliceImportDataFail(selectedYear, citizenId);
                                        //also delete from success table
                                        RemoveDataRowFromDataSuccessTable(citizenId);
                                    }
                                }
                                #endregion Import to XRayResult

                                #region AudiometryResultHistory
                                ////check
                                if (isCanContinueAfterImpportPolicesuccess)
                                {
                                    // if no data for audio do nothing
                                    if (!string.IsNullOrEmpty(row[16].ToString())
                                        || !string.IsNullOrEmpty(row[17].ToString())
                                        || !string.IsNullOrEmpty(row[18].ToString())
                                        || !string.IsNullOrEmpty(row[19].ToString())
                                        || !string.IsNullOrEmpty(row[20].ToString())
                                        || !string.IsNullOrEmpty(row[22].ToString())
                                        || !string.IsNullOrEmpty(row[23].ToString())
                                        || !string.IsNullOrEmpty(row[24].ToString())
                                        || !string.IsNullOrEmpty(row[25].ToString())
                                        || !string.IsNullOrEmpty(row[26].ToString())
                                        || !string.IsNullOrEmpty(row[27].ToString()))
                                    {

                                        AudiometryResultHistory audiometryResultHistory = new AudiometryResultHistory();
                                        audiometryResultHistory.police = police;
                                        audiometryResultHistory.police_fullname = fullPoliceName;
                                        audiometryResultHistory.year = selectedYear;
                                        if (isPoliceAgeConvertsionSuccess)
                                        {
                                            audiometryResultHistory.police_age = short.Parse(intPoliceAge.ToString());
                                        }
                                        else if (IsNeedToSetMinValue(strPoliceAge))
                                        {
                                            audiometryResultHistory.police_age = short.MinValue;
                                        }

                                        // dB_left_1000
                                        string strdB_left_1000 = row[18].ToString();
                                        int intdB_left_1000;
                                        bool isdB_left_1000ConvertsionSuccess = int.TryParse(strdB_left_1000, out intdB_left_1000);
                                        if (isdB_left_1000ConvertsionSuccess)
                                        {
                                            audiometryResultHistory.dB_left_1000 = intdB_left_1000;
                                        }
                                        else if (IsNeedToSetMinValue(strdB_left_1000))
                                        {
                                            audiometryResultHistory.dB_left_1000 = int.MinValue;
                                        }

                                        // dB_left_250
                                        string strdB_left_250 = row[16].ToString();
                                        int intdB_left_250;
                                        bool isdB_left_250ConvertsionSuccess = int.TryParse(strdB_left_250, out intdB_left_250);
                                        if (isdB_left_250ConvertsionSuccess)
                                        {
                                            audiometryResultHistory.dB_left_250 = intdB_left_250;
                                        }
                                        else if (IsNeedToSetMinValue(strdB_left_250))
                                        {
                                            audiometryResultHistory.dB_left_250 = int.MinValue;
                                        }

                                        // dB_left_2000
                                        string strdB_left_2000 = row[19].ToString();
                                        int intdB_left_2000;
                                        bool isdB_left_2000ConvertsionSuccess = int.TryParse(strdB_left_2000, out intdB_left_2000);
                                        if (isdB_left_2000ConvertsionSuccess)
                                        {
                                            audiometryResultHistory.dB_left_2000 = intdB_left_2000;
                                        }
                                        else if (IsNeedToSetMinValue(strdB_left_2000))
                                        {
                                            audiometryResultHistory.dB_left_2000 = int.MinValue;
                                        }

                                        // dB_left_4000
                                        string strdB_left_4000 = row[20].ToString();
                                        int intdB_left_4000;
                                        bool isdB_left_4000ConvertsionSuccess = int.TryParse(strdB_left_4000, out intdB_left_4000);
                                        if (isdB_left_4000ConvertsionSuccess)
                                        {
                                            audiometryResultHistory.dB_left_4000 = intdB_left_4000;
                                        }
                                        else if (IsNeedToSetMinValue(strdB_left_4000))
                                        {
                                            audiometryResultHistory.dB_left_4000 = int.MinValue;
                                        }

                                        // dB_left_500
                                        string strdB_left_500 = row[17].ToString();
                                        int intdB_left_500;
                                        bool isdB_left_500ConvertsionSuccess = int.TryParse(strdB_left_500, out intdB_left_500);
                                        if (isdB_left_500ConvertsionSuccess)
                                        {
                                            audiometryResultHistory.dB_left_500 = intdB_left_500;
                                        }
                                        else if (IsNeedToSetMinValue(strdB_left_500))
                                        {
                                            audiometryResultHistory.dB_left_500 = int.MinValue;
                                        }

                                        // dB_left_8000
                                        string strdB_left_8000 = row[21].ToString();
                                        int intdB_left_8000;
                                        bool isdB_left_8000ConvertsionSuccess = int.TryParse(strdB_left_8000, out intdB_left_8000);
                                        if (isdB_left_8000ConvertsionSuccess)
                                        {
                                            audiometryResultHistory.dB_left_8000 = intdB_left_8000;
                                        }
                                        else if (IsNeedToSetMinValue(strdB_left_8000))
                                        {
                                            audiometryResultHistory.dB_left_8000 = int.MinValue;
                                        }

                                        // dB_right_1000
                                        string strdB_right_1000 = row[24].ToString();
                                        int intdB_right_1000;
                                        bool isdB_right_1000ConvertsionSuccess = int.TryParse(strdB_right_1000, out intdB_right_1000);
                                        if (isdB_right_1000ConvertsionSuccess)
                                        {
                                            audiometryResultHistory.dB_right_1000 = intdB_right_1000;
                                        }
                                        else if (IsNeedToSetMinValue(strdB_right_1000))
                                        {
                                            audiometryResultHistory.dB_right_1000 = int.MinValue;
                                        }

                                        // dB_right_250
                                        string strdB_right_250 = row[22].ToString();
                                        int intdB_right_250;
                                        bool isdB_right_250ConvertsionSuccess = int.TryParse(strdB_right_250, out intdB_right_250);
                                        if (isdB_right_250ConvertsionSuccess)
                                        {
                                            audiometryResultHistory.dB_right_250 = intdB_right_250;
                                        }
                                        else if (IsNeedToSetMinValue(strdB_right_250))
                                        {
                                            audiometryResultHistory.dB_right_250 = int.MinValue;
                                        }


                                        // dB_right_2000
                                        string strdB_right_2000 = row[25].ToString();
                                        int intdB_right_2000;
                                        bool isdB_right_2000ConvertsionSuccess = int.TryParse(strdB_right_2000, out intdB_right_2000);
                                        if (isdB_right_2000ConvertsionSuccess)
                                        {
                                            audiometryResultHistory.dB_right_2000 = intdB_right_2000;
                                        }
                                        else if (IsNeedToSetMinValue(strdB_right_2000))
                                        {
                                            audiometryResultHistory.dB_right_2000 = int.MinValue;
                                        }


                                        // dB_right_4000
                                        string strdB_right_4000 = row[26].ToString();
                                        int intdB_right_4000;
                                        bool isdB_right_4000ConvertsionSuccess = int.TryParse(strdB_right_4000, out intdB_right_4000);
                                        if (isdB_right_4000ConvertsionSuccess)
                                        {
                                            audiometryResultHistory.dB_right_4000 = intdB_right_4000;
                                        }
                                        else if (IsNeedToSetMinValue(strdB_right_4000))
                                        {
                                            audiometryResultHistory.dB_right_4000 = int.MinValue;
                                        }


                                        // dB_right_500
                                        string strdB_right_500 = row[23].ToString();
                                        int intdB_right_500;
                                        bool isdB_right_500ConvertsionSuccess = int.TryParse(strdB_right_500, out intdB_right_500);
                                        if (isdB_right_500ConvertsionSuccess)
                                        {
                                            audiometryResultHistory.dB_right_500 = intdB_right_500;
                                        }
                                        else if (IsNeedToSetMinValue(strdB_right_500))
                                        {
                                            audiometryResultHistory.dB_right_500 = int.MinValue;
                                        }

                                        // dB_right_8000
                                        string strdB_right_8000 = row[27].ToString();
                                        int intdB_right_8000;
                                        bool isdB_right_8000ConvertsionSuccess = int.TryParse(strdB_right_8000, out intdB_right_8000);
                                        if (isdB_right_8000ConvertsionSuccess)
                                        {
                                            audiometryResultHistory.dB_right_8000 = intdB_right_8000;
                                        }
                                        else if (IsNeedToSetMinValue(strdB_right_8000))
                                        {
                                            audiometryResultHistory.dB_right_8000 = int.MinValue;
                                        }

                                        audiometryResultHistory.organization = objOrganization;

                                        bool isAudiometryResultHistorySuccess = audiometryResultHistory.insertPoliceAudiometryResultHistory(audiometryResultHistory);

                                        if (isAudiometryResultHistorySuccess)
                                        {
                                            isCanContinueAfterImpportPolicesuccess = true;
                                        }
                                        else
                                        {
                                            isCanContinueAfterImpportPolicesuccess = false;

                                            failImportPolicesDt.Rows.Add(orderNumber, citizenId, fullPoliceName, "ไม่สามารถบันทึกข้อมูล thaudiometry_result_history ได้");
                                            // delete police is import year fail
                                            police.DeleteAllPoliceImportDataFail(selectedYear, police);
                                            police.deletePoliceImportDataFail(selectedYear, citizenId);
                                            //also delete from success table
                                            RemoveDataRowFromDataSuccessTable(citizenId);
                                        }
                                    }
                                }

                                #endregion

                                #region UrineResultHistory
                                ///check
                                if (isCanContinueAfterImpportPolicesuccess)
                                {
                                    UrineResultHistory urineResultHistory = new UrineResultHistory();

                                    urineResultHistory.police = police;
                                    if (isPoliceAgeConvertsionSuccess)
                                    {
                                        urineResultHistory.police_age = short.Parse(intPoliceAge.ToString());
                                    }
                                    urineResultHistory.year = selectedYear;
                                    urineResultHistory.police_full_name = fullPoliceName;

                                    // PH
                                    string strPH = row[57].ToString();
                                    double dobPH;
                                    bool isPHConvertsionSuccess = double.TryParse(strPH, out dobPH);
                                    if (isPHConvertsionSuccess)
                                    {
                                        urineResultHistory.ph = dobPH;
                                    }
                                    else if (IsNeedToSetMinValue(strPH))
                                    {
                                        urineResultHistory.ph = double.MinValue;
                                    }

                                    //color
                                    string strColor = row[55].ToString();
                                    PoliceHealthClass.Color color = new PoliceHealthClass.Color();

                                    if (string.IsNullOrEmpty(strColor) || strColor == "ไม่ได้ส่งปัสสาวะ")
                                    {
                                        color = color.getColorByEngName("N/A");
                                    }
                                    else
                                    {
                                        color = color.getColorByEngName(strColor);
                                    }
                                    urineResultHistory.urine_color = color;

                                    //Appearance
                                    string strApperance = row[56].ToString();
                                    PoliceHealthClass.Appearance appearance = new PoliceHealthClass.Appearance();
                                    if (!string.IsNullOrEmpty(strApperance))
                                    {
                                        appearance = appearance.getAppearanceByNameEng(strApperance);
                                    }
                                    else
                                    {
                                        appearance = appearance.getAppearanceByNameEng("N/A");
                                    }
                                    urineResultHistory.urine_appearance = appearance;

                                    //spsugar                                                                

                                    string strspSugar = row[58].ToString();
                                    double dobspSugar;
                                    bool isspSugarConvertsionSuccess = double.TryParse(strspSugar, out dobspSugar);
                                    if (isspSugarConvertsionSuccess)
                                    {
                                        urineResultHistory.spgr = Math.Round(dobspSugar, 3);
                                    }
                                    else if (IsNeedToSetMinValue(strspSugar))
                                    {
                                        urineResultHistory.spgr = double.MinValue;
                                    }

                                    // Albumin
                                    string strUrineAlbumin = row[59].ToString();
                                    UrineAlbumin urineAlbumin = new UrineAlbumin();
                                    if (!string.IsNullOrEmpty(strUrineAlbumin))
                                    {
                                        urineAlbumin = urineAlbumin.getUrineAlbuminByNameEng(strUrineAlbumin);
                                    }
                                    else
                                    {
                                        urineAlbumin = urineAlbumin.getUrineAlbuminByNameEng("N/A");
                                    }
                                    urineResultHistory.urine_albumin = urineAlbumin;

                                    //Sugar
                                    string strUrineSugar = row[60].ToString();
                                    UrineSugar urineSugar = new UrineSugar();
                                    if (!string.IsNullOrEmpty(strUrineSugar))
                                    {
                                        urineSugar = urineSugar.getUrineSugarByNameEng(strUrineSugar);
                                    }
                                    else
                                    {
                                        urineSugar = urineSugar.getUrineSugarByNameEng("N/A");
                                    }
                                    urineResultHistory.urine_sugar = urineSugar;

                                    if (urineResultHistory.ph != Double.MinValue)
                                    {
                                        //RBC 
                                        string strRBC = row[61].ToString();
                                        //urineResultHistory.rbc = ConvertTreeDigitToFiveDigitString(strRBC);
                                        urineResultHistory.rbc = ConvertMicroscopic(strRBC);

                                        //WBC 
                                        string strWBC = row[62].ToString();
                                        //urineResultHistory.wbc = ConvertTreeDigitToFiveDigitString(strWBC);
                                        urineResultHistory.wbc = ConvertMicroscopic(strWBC);

                                        //Epitheliumcell 
                                        string strEpitheliumcell = row[63].ToString();
                                        //urineResultHistory.epithelium_cell = ConvertTreeDigitToFiveDigitString(strEpitheliumcell);
                                        urineResultHistory.epithelium_cell = ConvertMicroscopic(strEpitheliumcell);
                                    }

                                    // generalinfo
                                    urineResultHistory.year = selectedYear;
                                    urineResultHistory.police = police;
                                    urineResultHistory.police_full_name = fullPoliceName;
                                    if (isPoliceAgeConvertsionSuccess)
                                    {
                                        urineResultHistory.police_age = short.Parse(intPoliceAge.ToString());
                                    }
                                    else if (IsNeedToSetMinValue(strPoliceAge))
                                    {
                                        urineResultHistory.police_age = int.MinValue;
                                    }

                                    bool isInsertUrineResultHistorySuccess = urineResultHistory.insertPoliceUrineResultHistory(urineResultHistory);

                                    if (isInsertUrineResultHistorySuccess)
                                    {
                                        isCanContinueAfterImpportPolicesuccess = true;

                                        urineResultHistory.insertUrineAbnormalSummaryReportValue(selectedYear, selectedOrganizationId);
                                        urineResultHistory.insertUrineSummaryReportValue(selectedYear, selectedOrganizationId);
                                    }
                                    else
                                    {
                                        isCanContinueAfterImpportPolicesuccess = false;

                                        failImportPolicesDt.Rows.Add(orderNumber, citizenId, fullPoliceName, "ไม่สามารถบันทึกข้อมูล thurine_result_history ได้");
                                        // delete police is import year fail
                                        police.DeleteAllPoliceImportDataFail(selectedYear, police);
                                        police.deletePoliceImportDataFail(selectedYear, citizenId);
                                        //also delete from success table
                                        RemoveDataRowFromDataSuccessTable(citizenId);
                                    }
                                }

                                #endregion

                                #region Add thcancer_result_history
                                ////check
                                if (isCanContinueAfterImpportPolicesuccess)
                                {
                                    CancerResultHistory cancerResultHistory = new CancerResultHistory();

                                    // AFP
                                    string strAfp = row[82].ToString();
                                    decimal decAfp;
                                    bool isAfpConvertsionSuccess = Decimal.TryParse(strAfp, out decAfp);
                                    if (isAfpConvertsionSuccess)
                                    {
                                        cancerResultHistory.Afp = decAfp;
                                    }
                                    else if (IsNeedToSetMinValue(strAfp))
                                    {
                                        cancerResultHistory.Afp = decimal.MinValue;
                                    }

                                    // PSA
                                    string strPsa = row[83].ToString();
                                    decimal decPsa;
                                    bool isPsaConvertsionSuccess = Decimal.TryParse(strPsa, out decPsa);
                                    if (isPsaConvertsionSuccess)
                                    {
                                        cancerResultHistory.Psa = decPsa;
                                    }
                                    else if (IsNeedToSetMinValue(strPsa))
                                    {
                                        cancerResultHistory.Psa = decimal.MinValue;
                                    }

                                    // CEA
                                    string strCea = row[84].ToString();
                                    decimal decCea;
                                    bool isCeaConvertsionSuccess = Decimal.TryParse(strCea, out decCea);
                                    if (isCeaConvertsionSuccess)
                                    {
                                        cancerResultHistory.Cea = decCea;
                                    }
                                    else if (IsNeedToSetMinValue(strCea))
                                    {
                                        cancerResultHistory.Cea = decimal.MinValue;
                                    }

                                    // ca 125
                                    string strCa = row[85].ToString();
                                    decimal decCa;
                                    bool isCaConvertsionSuccess = Decimal.TryParse(strCa, out decCa);
                                    if (isCaConvertsionSuccess)
                                    {
                                        cancerResultHistory.Ca_125 = decCa;
                                    }
                                    else if (IsNeedToSetMinValue(strCa))
                                    {
                                        cancerResultHistory.Ca_125 = decimal.MinValue;
                                    }

                                    cancerResultHistory.CreatedUser = "Import Progarm";
                                    cancerResultHistory.OrganizationID = selectedOrganizationId;
                                    cancerResultHistory.OrganzationName = selectedOrganzationName;
                                    cancerResultHistory.FullName = fullPoliceName;
                                    cancerResultHistory.CitizenID = citizenId;

                                    if (isPoliceAgeConvertsionSuccess)
                                    {
                                        cancerResultHistory.Age = intPoliceAge;
                                    }

                                    cancerResultHistory.Year = selectedYear;
                                    cancerResultHistory.UpdateDate = DateTime.Now;
                                    cancerResultHistory.CreateDate = DateTime.Now;
                                    //Insert Cancer Result History
                                    string errMessage = string.Empty;

                                    bool isInsertCancerResultHistorySuccess = CancerResultHistory.InsertPoliceCancerResultHistory(cancerResultHistory, ref errMessage);

                                    if (isInsertCancerResultHistorySuccess)
                                    {
                                        isCanContinueAfterImpportPolicesuccess = true;
                                    }
                                    else
                                    {
                                        isCanContinueAfterImpportPolicesuccess = false;

                                        failImportPolicesDt.Rows.Add(orderNumber, citizenId, fullPoliceName, "ไม่สามารถบันทึกข้อมูลผลมะเร็งได้");
                                        // delete police is import year fail
                                        police.DeleteAllPoliceImportDataFail(selectedYear, police);
                                        police.deletePoliceImportDataFail(selectedYear, citizenId);
                                        //also delete from success table
                                        RemoveDataRowFromDataSuccessTable(citizenId);
                                    }
                                }

                                #endregion
                            }
                            else
                            {
                                failImportPolicesDt.Rows.Add(orderNumber, citizenId, fullPoliceName, "ไม่สามารถบันทึกข้อมูลในตาราง tbpolice ได้");
                                isValidDataAndReadyToImportPoliceTable = false;
                            }

                            #endregion Import Police
                        }

                        rowCounter++;
                    }
                }      

                #endregion

                BindSuccessAndFailGrid();

                SetStatus("นำเข้าข้อมูลเสร็จสิ้น");

                //reload all police
                allPolicesDt = objPolice.GetAllPoliceByYearAndOrganization(selectedYear, selectedOrganizationId).Tables[0];
            }
            else
            {
                MessageBox.Show("รูปแบบของไฟล์นำเข้าไม่ถูกต้อง", "ตรวจสอบข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

        }

        private String ConvertMicroscopic(String microscopic)
        {
            String outResult = String.Empty;
            try
            {
                String[] tmpMicroscopic = microscopic.Replace(" ", "").Split(new Char[] { '-' }, 2);
                outResult = String.Format("{0}-{1}", tmpMicroscopic[0].PadLeft(3, '0'), tmpMicroscopic[1].PadLeft(3, '0'));
            }
            catch (Exception)
            {
                outResult = "000-000";
            }

            return outResult;
        }

        private string ConvertTreeDigitToFiveDigitString(string strInData)
        {
            string convertedData = string.Empty;

            if (!string.IsNullOrEmpty(strInData) && strInData.Length == 3)
            {
                try
                {
                    string firstNumber = string.Format("{0:00}", int.Parse(strInData[0].ToString()));
                    string secondNumber = string.Format("{0:00}", int.Parse(strInData[2].ToString()));
                    convertedData = firstNumber + "-" + secondNumber;
                }
                catch (Exception ex)
                {
                    convertedData = string.Empty;
                }
            }
            else
            {
                convertedData = "00-00";
            }

            return convertedData;
        }

        private void BindOrganizatoinTree()
        {
            PoliceHealthClass.Organization OrganizationBuffer = new PoliceHealthClass.Organization();
            //OrganizationBuffer.LoadAllOrganizationAll();

            //if (OrganizationBuffer.NextRecord())
            //{
            //    trvImportOrganization.Nodes.Add(OrganizationBuffer.organization_name_tha);
            //    trvImportOrganization.Nodes[0].Tag = OrganizationBuffer.organization_id;
            //    GenerateOrganization(trvImportOrganization.Nodes[0], OrganizationBuffer.organization_id);
            //    trvImportOrganization.Nodes[0].Expand();
            //}

            ArrayList OrganizationArr = OrganizationBuffer.getOrganizationRoot();

            if (OrganizationArr.Count > 0)
            {
                Int32 iNode = 0;

                foreach (Organization org in OrganizationArr)
                {
                    trvImportOrganization.Nodes.Add(org.organization_name_tha);
                    trvImportOrganization.Nodes[iNode].Tag = org.organization_id;
                    GenerateOrganization(trvImportOrganization.Nodes[iNode], org.organization_id);
                    trvImportOrganization.Nodes[iNode].Expand();
                    iNode++;
                }
            }
        }

        private void BindingDropDownYear()
        {
            cbbImportYear.DataSource = manageImportPoliceHelathData.getImportDataYear().Tables[0];
            cbbImportYear.DisplayMember = "year";
            cbbImportYear.ValueMember = "year";
        }

        private void ClearDataGridAndDataTable()
        {
            dgvImportSuccess.DataSource = false;
            dgvImportFail.DataSource = false;

            successImportPolicesDt.Clear();
            failImportPolicesDt.Clear();

        }

        #endregion

        #region Event Handlers

        public frmImportPoliceHealthData()
        {
            InitializeComponent();
        }

        private void frmImportPoliceHealthData_Load(object sender, EventArgs e)
        {
            SetStatus("ว่าง");

            BindingDropDownYear();
            BindOrganizatoinTree();
            GenarateThaimounths();
            PrepareSuccessAndFailDataTableColumn();

            btnImportData.Enabled = false;
        }

        private void btnImportData_Click(object sender, EventArgs e)
        {
            if (IsSelectValidOrganizatonTreeNode())
            {
                ImportDataFromExcelToTable();
            }
            else
            {
                MessageBox.Show("กรณาเลือกหน่วยงานให้ถูกต้อง", "ตรวจสอบข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

        }

        private void btnSelectFile_Click(object sender, EventArgs e)
        {
            if (this.openFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                btnImportData.Enabled = true;
            }
        }

        private void trvImportOrganization_AfterSelect(object sender, TreeViewEventArgs e)
        {
            if (IsSelectValidOrganizatonTreeNode())
            {
                this.Text = "นำข้อมูลเข้า: " + trvImportOrganization.SelectedNode.Text;
            }
        }

        #endregion

        private void lnkShowSuccessReport_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {

            Guid g = Guid.NewGuid();
            string exportath = System.Windows.Forms.Application.StartupPath;
            string fileName = string.Format("SuccessImportHealthPolice_{0}.xls", g.ToString());
            string fullFilePathAndName = exportath + @"\" + fileName;


            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.InitialDirectory = Convert.ToString(Environment.SpecialFolder.MyDocuments);
            saveFileDialog1.Filter = "Excel file(*.xls)|*.xls";
            saveFileDialog1.FilterIndex = 1;

            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string[] columnsName = { "ลำดับที่", "เลขบัตรประชาชน", "ชื่อ - นามสกุล" };
                DataTable tempDt = ChangeDatatableColumnName(successImportPolicesDt, columnsName);

                ExcelReader excelReader = new ExcelReader();
                excelReader.ExportDataTableToExcel(tempDt, saveFileDialog1.FileName);
            }
        }

        private void lnkShowFailReport_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.InitialDirectory = Convert.ToString(Environment.SpecialFolder.MyDocuments);
            saveFileDialog1.Filter = "Excel file(*.xls)|*.xls";
            saveFileDialog1.FilterIndex = 1;

            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string[] columnsName = { "ลำดับที่", "เลขบัตรประชาชน", "ชื่อ - นามสกุล", "หมายเหตุ" };
                DataTable tempDt = ChangeDatatableColumnName(failImportPolicesDt, columnsName);

                ExcelReader excelReader = new ExcelReader();
                excelReader.ExportDataTableToExcel(tempDt, saveFileDialog1.FileName);

            }

        }

        private DataTable ChangeDatatableColumnName(DataTable dt, string[] columnsName)
        {
            DataTable tmp = dt.Copy();

            int toTalDataTableColomn = failImportPolicesDt.Columns.Count;

            for (int i = 0; i < columnsName.Length; i++)
            {
                if (i <= toTalDataTableColomn)
                {
                    tmp.Columns[i].ColumnName = columnsName[i];
                }
                else
                {
                    break;
                }
            }

            return tmp;
        }

        #region Export excel call

        private void SetStatus(string status)
        {
            lblStatus.Text = status;
            lblStatus.Invalidate();
            lblStatus.Update();
            lblStatus.Refresh();
            Application.DoEvents();
        }

        private void SetPageProgressText(int currentReccord, int allRecord)
        {
            string strRowstatus = currentReccord.ToString() + " จาก " + allRecord.ToString() + " รายการ";
            lblRowIndication.Text = strRowstatus;
            lblStatus.Invalidate();
            lblStatus.Update();
            lblStatus.Refresh();
            Application.DoEvents();
        }

        public DataTable GetDataTableFromExcel(string excelFile, int firstDataRow)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range usedRange;

            int rowCounter = 0;
            int columnCounter = 0;
            int totalRows = 0;
            int totalColumns = 0;
            int currentPercent = 0;


            DataTable excelDatatable = new DataTable();
            List<string> columnNames = new List<string>();

            xlApp = new Excel.Application();

            try
            {
                xlWorkBook = xlApp.Workbooks.Open(excelFile, 0, true, 5, ""
                                                , "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false
                                                , false, 0, true, 1, 0);
                //Get first worksheet
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                //Convert to Excel.Range object
                usedRange = xlWorkSheet.UsedRange;

                totalRows = usedRange.Rows.Count;
                totalColumns = usedRange.Columns.Count;

                while (excelDatatable.Columns.Count < totalColumns)
                {
                    excelDatatable.Columns.Add();
                }

                int progressCounter = 1;
                int progressTotalRow = totalRows - (firstDataRow - 1);

                for (rowCounter = firstDataRow; rowCounter <= totalRows; rowCounter++)
                {                  
                    // Prepare array for data row
                    object[] array = new object[totalColumns];

                    for (columnCounter = 0; columnCounter < totalColumns; columnCounter++)
                    {
                        array[columnCounter] = (usedRange.Cells[rowCounter, columnCounter + 1] as Excel.Range).Value2;
                    }

                    // Add row to data table
                    excelDatatable.Rows.Add(array);

                    //progress bar
                    currentPercent = (progressCounter * 100) / progressTotalRow;
                    SetPageProgressText(progressCounter, progressTotalRow);
                    UpdateProgress(currentPercent);
                    progressCounter++;

                }

                //Close excel file without save
                xlWorkBook.Close(false, Type.Missing, Type.Missing);
                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error" + ex.ToString());
            }

            return excelDatatable;
        }

        public bool IsCorrectTemplate(DataTable data)
        {
            bool isCorrectTemplate = false;
            if (data != null)
            {
                isCorrectTemplate = (data.Columns.Count == 89);
            }
            return isCorrectTemplate;
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception exception)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + exception.ToString());
                throw exception;
            }
            finally
            {
                GC.Collect();
            }
        }

        //public void Dispose()
        //  {
        //      GC.Collect();
        //  }

        public bool ExportDataTableToExcel(DataTable dt, string filepath)
        {

            Excel.Application oXL;
            Excel.Workbook oWB;
            Excel.Worksheet oSheet;
            Excel.Range oRange;

            try
            {
                // Start Excel and get Application object. 
                oXL = new Excel.Application();

                // Set some properties 
                oXL.Visible = true;
                oXL.DisplayAlerts = false;

                // Get a new workbook. 
                oWB = oXL.Workbooks.Add(Missing.Value);

                // Get the Active sheet 
                oSheet = (Excel.Worksheet)oWB.ActiveSheet;
                oSheet.Name = "เอกสาร1";

                int rowCount = 1;
                foreach (DataRow dr in dt.Rows)
                {
                    rowCount += 1;
                    for (int i = 1; i < dt.Columns.Count + 1; i++)
                    {
                        // Add the header the first time through 
                        if (rowCount == 2)
                        {
                            oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                        }
                        oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                    }
                }

                // Resize the columns 
                oRange = oSheet.get_Range(oSheet.Cells[1, 1],
                              oSheet.Cells[rowCount, dt.Columns.Count]);
                oRange.EntireColumn.AutoFit();

                // Save the sheet and close 
                oSheet = null;
                oRange = null;
                oWB.SaveAs(filepath, Excel.XlFileFormat.xlWorkbookNormal,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Excel.XlSaveAsAccessMode.xlExclusive,
                    Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value);
                oWB.Close(Missing.Value, Missing.Value, Missing.Value);
                oWB = null;
                oXL.Quit();
            }
            catch
            {
                throw;
            }
            finally
            {
                // Clean up 
                // NOTE: When in release mode, this does the trick 
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
            }

            return true;
        }

        #endregion       

    }
}
